# _*_ coding:utf-8 _*_
# @Time  : 2022.07.15
# @Author: zizlee
import time
import datetime
import pathlib
import shutil
import sys
import MySQLdb
from MySQLdb.converters import conversions
from MySQLdb.constants import FIELD_TYPE

from db import FAConnection
from configs import FILE_STORAGE
from v1_all_api.all_utils import file_utils


def mysqlclient_converters():
    conversions[FIELD_TYPE.BIT] = lambda x: 1 if int.from_bytes(x, byteorder=sys.byteorder, signed=False) else 0
    return conversions


params = {
    'port': 3306,
    'user': 'root',
    'password': 'mysql',
    'database': 'worker_recorder'
}


work_db = MySQLdb.connect(charset='utf8', conv=mysqlclient_converters(), **params)
cursor = work_db.cursor(MySQLdb.cursors.DictCursor)
cursor.execute("SELECT a.*,b.username FROM work_article As a INNER JOIN user_user As b ON a.author_id=b.id;")
records = cursor.fetchall()

# 查询用户
ruizy_db = FAConnection()

user_list = ruizy_db.query('SELECT admin_name,id FROM ruizy_user WHERE is_analyst=1;', keep_conn=True)
user_id_dict = {u['admin_name']: u['id'] for u in user_list}

# 处理数据，处理ID
save_list = []
# 过滤掉傅彬彬的记录
records = list(filter(lambda x: x['username'] != '傅彬彬', records))
levels = {5: 'A', 4: 'B', 3: 'C', 2: 'D', 1: 'E'}
for r in records:
    new_user_id = user_id_dict.get(r['username'])
    if not new_user_id:
        raise ValueError('new user_id not exists!')
    r['user_id'] = new_user_id
    r['create_date'] = datetime.datetime.fromtimestamp(r['create_time']).strftime('%Y%m%d')
    r['examine_state'] = 1
    r['level_flag'] = levels.get(r['score'])
    if not r['level_flag']:
        raise ValueError('level not found!')
    r['evaluator'] = r['checker']
    # 处理文章类别
    if r['media_name'].find('号')>-1:
        r['article_type'] = 'T02'
    else:
        r['article_type'] = 'T03' if r['media_name'] else 'T11'
    if r['annex_url']:
        # 将旧文件复制
        old_annex = pathlib.Path('D:/WK/').joinpath(r['annex_url'])
        if r['create_date']<'20210106' or r['id'] in [728, 729, 730, 731]:
            # 处理附件路径
            r['annex_url'] = r['annex_url'].replace('artpublish', f'ArticleWriting/{r["create_date"][:6]}')
        else:
            # 处理附件路径
            r['annex_url'] = r['annex_url'].replace(f'ARTICLE/{r["author_id"]}', 'ArticleWriting')
        new_annex = pathlib.Path(FILE_STORAGE).joinpath(r['annex_url'])
        if not new_annex.parent.exists():
            new_annex.parent.mkdir(parents=True)

        # 生成新的文件名称
        new_filename = file_utils.generate_unique_filename(file_folder=str(new_annex.parent) + '/',
                                                           filename=old_annex.name.replace(old_annex.suffix, ''),
                                                           suffix=old_annex.suffix.replace('.', ''), hashed=True)
        new_filepath = new_filename[0].replace('\\', '/') + new_filename[1] + '.' + new_filename[2]

        shutil.copyfile(old_annex, new_filepath)
        r['annex_url'] = new_filepath.replace(FILE_STORAGE, '')
        print(r['annex'],'复制完成')
        time.sleep(0.25)
    save_list.append(r)
cursor.close()
work_db.close()

# 保存数据
insert_sql = """
    INSERT INTO work_article_writing (create_date,user_id,title,article_type,media_name,rough_type,words,
    allowance,partner,note,annex,annex_url,is_publish,examine_state,level_flag,evaluator) VALUES
    (%(create_date)s,%(user_id)s,%(title)s,%(article_type)s,%(media_name)s,%(rough_type)s,%(words)s,
    %(allowance)s,%(partner)s,%(note)s,%(annex)s,%(annex_url)s,%(is_publish)s,%(examine_state)s,%(level_flag)s,
    %(evaluator)s);
"""
print(len(save_list))
c = ruizy_db.insert(insert_sql, param=save_list, many=True)
print('迁移成功!', c[0])

